<?php
    $db1 = '192.168.12.200';
    $db2 = '192.168.12.201:33306';
    $conn_dev = mysql_connect($db1, 'dev', 'dev');
    $conn_test = mysql_connect($db2, 'dev', 'dev');

    if (!$conn_dev) {
    	echo $db1.'connect failed'."\n";
    	return;
    }
    
    if (!$conn_test) {
    	echo $db2.'connect failed'."\n";
    	return;
    }
    
    mysql_select_db('genesis', $conn_dev);
    mysql_select_db('genesis', $conn_test);
    
    $rs_dev = mysql_query('show tables', $conn_dev);
    $rs_test = mysql_query('show tables', $conn_test);
    
    $tables_dev = array();
    while ($re = mysql_fetch_row($rs_dev)) {
    	$tables_dev[] = $re[0];
    }
    
    $tables_test = array();
    while ($re = mysql_fetch_row($rs_test)) {
    	$tables_test[] = $re[0];
    }
    
    /**
     * 新增的表
     */
    $table_delta = array_diff($tables_dev, $tables_test);
    $file = fopen('sql.txt', 'w+');
    fwrite($file, 'new tables:'." =======================================\r\n");
    foreach ($table_delta as $val) {
        fwrite($file, $val."\r\n");
    }
    fwrite($file, '============================================'."\r\n");
    foreach ($table_delta as $val) {
        $sql = "SHOW COLUMNS FROM $val";
        $rs = mysql_query($sql, $conn_dev);
        $createsql = 'CREATE TABLE IF NOT EXISTS `'.$val.'` (';
        $pri = '';
        while ($re = mysql_fetch_array($rs)) {
        	$createsql .= '`'.$re['Field'].'` ';
        	$createsql .= $re['Type'].' ';
        	if ($re['Null'] == 'NO') {
        		$createsql .= ' NOT NULL ';
        	}
        	if (!empty($re['Default'])) {
        	    $createsql .= 'DEFAULT \''.$re['Default'].'\' ';
        	}
        	
        	if(!empty($re['Extra'])) {
        	    $createsql .= $re['Extra'];
        	}
        	$createsql .= ',';
        	if ($re['Key'] == 'PRI') {
        		$pri = 'PRIMARY KEY (`'.$re['Field'].'`)';
        	}
        }
        $createsql .= $pri;
        $createsql .= ');';
//        echo $createsql ."\n";
//        fwrite($file, $createsql);
    }
    
    /**
     * 字段被修改
     */
    $table_intersect = array_intersect($tables_dev, $tables_test);
    foreach ($table_intersect as $val) {
        $sql = "SHOW COLUMNS FROM $val";
        $rs_dev = mysql_query($sql, $conn_dev);
        $rs_test = mysql_query($sql, $conn_test);
        
        $fields_dev = array();
        $fields_test = array();
        while ($re = mysql_fetch_array($rs_dev)) {
        	$fields_dev[$re[0]] = $re;
        }
        while ($re = mysql_fetch_array($rs_test)) {
        	$fields_test[$re[0]] = $re;
        }
        $field_diff = array_diff($fields_dev, $fields_test);
        
        if (!empty($field_diff)) {
            print_r($field_diff);
/*            fwrite($file, $val.'中被修改的字段'."\r\n================\r\n");
            foreach ($field_diff as $v) {
                print_r($re);
                fwrite($file, $v."\r\n");
                $sql = "ALTER TABLE `$val` CHANGE `$v` `$v`";
                
//                ALTER TABLE  `genesis_hotarea` CHANGE  `modified`  `modified` DATETIME NULL DEFAULT NULL;
            }
            fwrite($file, "\r\n================\r\n");*/
        }
    }
    
    fclose($file);